package com.wangzhou.tour.dao.implay;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.wangzhou.common.util.DbUtil;
import com.wangzhou.common.util.PageInfo;
import com.wangzhou.tour.dao.IAGoodsDAO;
import com.wangzhou.tour.pojo.AGoods;

/**
 * 管理员表的DAO实现类
 * @author zjx
 *
 */
public class AGoodsDAOImplay implements IAGoodsDAO
{
	/**
	 * 将数据库里面的记录变成pojo(java对象)
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private AGoods conver(ResultSet rs) throws SQLException
	{
		AGoods goods = new AGoods();
		goods.setId(rs.getInt("id"));
		goods.setCreateId(rs.getInt("createId"));
		goods.setCateId(rs.getInt("cateId"));
		goods.setCityId(rs.getInt("cityId"));
		goods.setName(rs.getString("name"));
		goods.setImgPath(rs.getString("imgPath"));
		goods.setBrief(rs.getString("brief"));
		goods.setTraffic(rs.getString("traffic"));
		goods.setGroup(rs.getString("group"));
		goods.setEndtime(rs.getTimestamp("endtime"));
		goods.setMarketPrice(rs.getDouble("marketPrice"));
		goods.setPrice(rs.getDouble("price"));
		goods.setViewCount(rs.getInt("viewCount"));
		goods.setStatus(rs.getByte("status"));
		goods.setCreatetime(rs.getTimestamp("createtime"));
		goods.setUpdatetime(rs.getTimestamp("updatetime"));
		goods.setPubtime(rs.getTimestamp("pubtime"));
		return goods;
	}
	
	@Override
	public int insert(AGoods goods)
	{
		Connection conn = null ; 
		PreparedStatement pst = null ; 
		ResultSet rs = null ; 
		/*
		 * insert into `a_goods` (`createId`, `cateId`, `cityId`, `name`, `imgPath`, `brief`, `traffic`, 
		 * `group`, `endtime`, `marketPrice`, `price`, `viewCount`, `status`, `createtime`, `updatetime`, `pubtime`) 
		 * values ('1', '1', '1', '1', '1', '1', '1', '1', '2016-05-14 18:13:10', '1', '1', '1', '1', '2016-05-14 18:13:13', 
		 * '2016-05-14 18:13:16', '2016-05-14 18:13:19')
		 * 
		 * Hibernate,
		 * Spring jdbc
		 * mybatis
		 */
		String sql = "insert into `a_goods` (`createId`, `cateId`, `cityId`, `name`, `imgPath`, `brief`, `traffic`, "
				+ "`group`, `endtime`, `marketPrice`, `price`, `viewCount`, `status`, `createtime`, `updatetime`, `pubtime`) "
				+ " values"
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?)" ; 
		try
		{
			//获得链接
			conn = DbUtil.INSTANCE().getConnection();
			pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			pst.setObject(1, goods.getCreateId());
			pst.setObject(2, goods.getCateId());
			pst.setObject(3, goods.getCityId());
			pst.setObject(4, goods.getName());
			pst.setObject(5, goods.getImgPath());
			pst.setObject(6, goods.getBrief());
			pst.setObject(7, goods.getTraffic());
			pst.setObject(8, goods.getGroup());
			pst.setObject(9, goods.getEndtime());
			pst.setObject(10, goods.getMarketPrice());
			pst.setObject(11, goods.getPrice());
			pst.setObject(12, goods.getViewCount());
			pst.setObject(13, goods.getStatus());
			pst.setObject(14, goods.getCreatetime());
			pst.setObject(15, goods.getUpdatetime());
			pst.setObject(16, goods.getPubtime());
			
			int res = pst.executeUpdate();
			System.out.println(this.getClass() + "--插入一条记录,影响条数:" + res);
			rs = pst.getGeneratedKeys();
			int id = 0 ; 
			if(rs.next())
			{
				id = rs.getInt(1);
				goods.setId(id);
			}
			return id ; 
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			DbUtil.INSTANCE().closeAll(conn, pst, rs);
		}
		return 0;
	}

	@Override
	public int update(String type,AGoods goods)
	{
		if("1".equalsIgnoreCase(type))
		{
			//按照id全部更新
			/*
			 * update `a_goods` SET `password`='222' where (`id`='6') LIMIT 1
			 */
			String sql = "update `a_goods` set `createId` = ?, `cateId` = ?, `cityId`= ?, `name`= ?, `imgPath`= ?, `brief`= ?, `traffic`= ?, "
				+ "`group`= ?, `endtime`= ?, `marketPrice`= ?, `price`= ?, `viewCount`= ?, `status`= ?, `updatetime`= ?, "
				+ "`pubtime`= ? "
					+ " where `id`= ? " ; 
			int res = 0;
			Connection conn = null ;
			PreparedStatement pst = null ;
			try
			{
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, goods.getCreateId());
				pst.setObject(2, goods.getCateId());
				pst.setObject(3, goods.getCityId());
				pst.setObject(4, goods.getName());
				pst.setObject(5, goods.getImgPath());
				pst.setObject(6, goods.getBrief());
				pst.setObject(7, goods.getTraffic());
				pst.setObject(8, goods.getGroup());
				pst.setObject(9, goods.getEndtime());
				pst.setObject(10, goods.getMarketPrice());
				pst.setObject(11, goods.getPrice());
				pst.setObject(12, goods.getViewCount());
				pst.setObject(13, goods.getStatus());
				pst.setObject(14, goods.getUpdatetime());
				pst.setObject(15, goods.getPubtime());
				pst.setObject(16, goods.getId());
				res = pst.executeUpdate();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, null);
			}
			return res ; 
		}
		return 0;
	}

	@Override
	public int delete(String type,AGoods goods)
	{
		return 0;
	}

	@Override
	public AGoods findOne(String type, Map<String, Object> paramsMap)
	{
		if("1".equalsIgnoreCase(type))
		{
			//按照id查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			String sql = "select * from a_goods where id = ? " ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, paramsMap.get("id") + "");
				
				rs = pst.executeQuery();
				//结果集肯定只有一条
				if(rs.next())
				{
					AGoods goods = conver(rs);
					return goods ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}else if("2".equalsIgnoreCase(type))
		{
			//按照email查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			String sql = "select * from a_goods where email = ? " ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				pst = conn.prepareStatement(sql);
				pst.setObject(1, paramsMap.get("email") + "");
				
				rs = pst.executeQuery();
				//结果集肯定只有一条
				if(rs.next())
				{
					AGoods goods = conver(rs);
					return goods ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}
		return null ; 
	}

	@Override
	public List<AGoods> findCondList(PageInfo pageInfo, String type, Map<String, Object> paramsMap)
	{
		List<AGoods> goodsList = new ArrayList<AGoods>();
		if("1".equalsIgnoreCase(type))
		{
			//按照id查询
			Connection conn = null ; 
			PreparedStatement pst = null ; 
			ResultSet rs = null ; 
			StringBuffer sqlsb = new StringBuffer();
			sqlsb.append("select * from a_goods ad where 1 = 1 ") ; 
			try
			{
				//获得链接
				conn = DbUtil.INSTANCE().getConnection();
				
				List paramList = new ArrayList();
				if(paramsMap.get("keyword") != null && !"".equalsIgnoreCase(paramsMap.get("keyword") + ""))
				{
					//关键字查询
					sqlsb.append(" and (ad.name like ?)");
					paramList.add("%" + paramsMap.get("keyword") + "%");
				}
				if(paramsMap.get("status") != null && !"".equalsIgnoreCase(paramsMap.get("status") + ""))
				{
					//按照状态查询
					sqlsb.append(" and ad.status = ? ");
					paramList.add(paramsMap.get("status"));
				}
				
				if(paramsMap.get("stDate") instanceof Date && paramsMap.get("edDate") instanceof Date)
				{
					//按照时间范围查询
					sqlsb.append(" and ad.createtime >= ? and ad.createtime <= ? ");
					paramList.add(paramsMap.get("stDate"));
					paramList.add(paramsMap.get("edDate"));
				}
				
				if("viewCountdesc".equalsIgnoreCase(paramsMap.get("orderby") + ""))
				{
					sqlsb.append("order by viewCount desc");
				}else if("viewCountasc".equalsIgnoreCase(paramsMap.get("orderby") + ""))
				{
					sqlsb.append("order by viewCount asc");
				}else
				{
					sqlsb.append("order by updatetime desc");
				}
				
				//计算总条数
				/* 目的:
				 * 语句:select * from a_goods where 1 = 1 and status = 1; 
				 * 变成:select count(*) from a_admin where 1 = 1 and status = 1;
				 */
				String sqlIndex = "select *" ;
				String sqlCount = "select count(*) " + sqlsb.substring(sqlsb.indexOf(sqlIndex) + sqlIndex.length(), sqlsb.length());
				pst = conn.prepareStatement(sqlCount);
				int count = 1 ; 
				for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
				{
					Object obj = (Object) iterator.next();
					pst.setObject(count, obj);
					count ++ ; 
				}
				rs = pst.executeQuery();
				System.out.println("总条数的sql:" + sqlCount);
				//结果肯定有1条
				if(rs.next())
				{
					//设置总条数
					pageInfo.setTotalRecord(rs.getInt(1));
				}
				pst.close();
				rs.close();
				
				//如果有数据,再往下查询,否则不查询
				if(pageInfo.getTotalRecord() > 0)
				{
					//数据库设置条数
					sqlsb.append(" limit " + pageInfo.getCurrentRecord() + "," + pageInfo.getPageSize());
					System.out.println("分页的sql:" + sqlsb.toString());
					pst = conn.prepareStatement(sqlsb.toString());
					//pst.setObject(1, paramsMap.get("id") + "");
					//设置条件的值
					count = 1 ; 
					for (Iterator iterator = paramList.iterator(); iterator.hasNext();)
					{
						Object obj = (Object) iterator.next();
						pst.setObject(count, obj);
						count ++ ; 
					}
					rs = pst.executeQuery();
					//循环结果集
					while(rs.next())
					{
						AGoods goods = conver(rs);
						goodsList.add(goods);
					}
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}finally
			{
				DbUtil.INSTANCE().closeAll(conn, pst, rs);
			}
		}
		return goodsList ; 
	}
	
}
